--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';


 DELETE FROM se_ART_COMPDEPOT;
/*
select * from (
SELECT &outils.stragg(source) source,comp_num, depot_cod, compdepot_rang, prfsto_cod, prfrupt_cod, depotempl_cod, prfempl_cod, prflot_cod, prfserie_cod, prfidentstk_cod, prfinvent_cod, enreg_id, enreg_s, rgtcase_id, pckcase_id, emplfam_cod, fournisseur_cod, abc_cod, emplabc_cod, invtabc_cod, invtdern_dat, invtproch_dat, adrstock_cod, adrstock2_cod, adrstock3_cod, adrstock4_cod, adrstock5_cod, frequencenbs_val, fraisappro_mt, delaiplus_nb
FROM (
  SELECT 'uv' source ,g.* FROM se_ART_COMPDEPOT g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.ART_COMPDEPOT g, &erp.art_refe r where r.comp_num=g.comp_num
  )

GROUP BY  comp_num, depot_cod, compdepot_rang, prfsto_cod, prfrupt_cod, depotempl_cod, prfempl_cod, prflot_cod, prfserie_cod, prfidentstk_cod, prfinvent_cod, enreg_id, enreg_s, rgtcase_id, pckcase_id, emplfam_cod, fournisseur_cod, abc_cod, emplabc_cod, invtabc_cod, invtdern_dat, invtproch_dat, adrstock_cod, adrstock2_cod, adrstock3_cod, adrstock4_cod, adrstock5_cod, frequencenbs_val, fraisappro_mt, delaiplus_nb
HAVING Count(1)!=2 ) r
ORDER BY comp_num,depot_cod*/

--SELECT majsto,emplacement,MAGRGT,MAGPCK,CLASVOLUME,TYPERGT,p.* FROM uv_pproduits p WHERE id LIKE '54870771449%'
--SELECT * FROM &erp.art_compdepot WHERE comp_num =134299


/*d�pots des rgp a redescendre sur les complet stock*/
 INSERT INTO se_ART_COMPDEPOT
 (SELECT DISTINCT
         Nvl((SELECT comp_num FROM &erp.art_comp g WHERE g.combsto_id =p.numspecif),0) comp_num,
         LPad(dep.depot,2,'0') depot_cod,
         dep.RANGMULTI2+1 compdepot_rang,
         Nvl((SELECT prfsto_cod FROM &erp.art_compdepot d WHERE d.comp_num=g.comp_num AND d.depot_cod=dep.depot),'AA') prfsto_cod,
         Nvl((SELECT prfrupt_cod FROM &erp.art_compdepot d WHERE d.comp_num=g.comp_num AND d.depot_cod=dep.depot),'STMINI_ECO') prfrupt_cod,
         LPad(dep.depot,2,'0') depotempl_cod,
         Nvl((SELECT prfempl_cod FROM &erp.art_compdepot d WHERE d.comp_num=g.comp_num AND d.depot_cod=dep.depot),'AUCUN') prfempl_cod,
         Nvl((SELECT prflot_cod FROM &erp.art_compdepot d WHERE d.comp_num=g.comp_num AND d.depot_cod=dep.depot),'AUCUN') prflot_cod,
         Nvl((SELECT prfserie_cod FROM &erp.art_compdepot d WHERE d.comp_num=g.comp_num AND d.depot_cod=dep.depot),'AUCUN') prfserie_cod,
         Nvl((SELECT prfidentstk_cod FROM &erp.art_compdepot d WHERE d.comp_num=g.comp_num AND d.depot_cod=dep.depot),'AUCUN') prfidentstk_cod,
         Nvl((SELECT prfinvent_cod FROM &erp.art_compdepot d WHERE d.comp_num=g.comp_num AND d.depot_cod=dep.depot),'MANUEL') prfinvent_cod,
         0 enreg_id,
         'N' enreg_s,
         NULL rgtcase_id,
         NULL pckcase_id,
         NULL emplfam_cod,
         NULL fournisseur_cod,
         NULL abc_cod,
         NULL emplabc_cod,
         NULL invtabc_cod,
         NULL invtdern_dat,
         NULL invtproch_dat,
         NULL adrstock_cod,
         NULL adrstock2_cod,
         NULL adrstock3_cod,
         NULL adrstock4_cod,
         NULL adrstock5_cod,
         Decode(prgp.frequence,0,NULL) frequencenbs_val,
         NULL fraisappro_mt,
         NULL delaiplus_nb
  FROM uv_pproduits_depot dep, uv_pproduits prgp,uv_pproduits p,uv_pproduits_statut_actuel s
      ,&erp.art_gene g
  WHERE prgp.id=dep.id
    AND p.clergp=prgp.id
    AND s.id=prgp.id AND (s.statut<900 OR s.statut IS NULL)
    AND prgp.codgestion in ('5')
    AND p.codgestion IN ('3')
    AND NOT EXISTS (SELECT 1 FROM uv_pproduits_depot depp WHERE depp.id=p.id AND depp.depot=dep.depot)
    AND (g.gene_cod||g.gene_ind)=p.prodbase
 );


/*d�pots des complet stock de progres*/
 INSERT INTO se_ART_COMPDEPOT
 (SELECT DISTINCT
         Nvl((SELECT comp_num FROM &erp.art_comp g WHERE g.combsto_id =p.numspecif),0) comp_num,
         LPad(dep.depot,2,'0') depot_cod,
         dep.RANGMULTI2+1 compdepot_rang,
         Nvl((SELECT prfsto_cod FROM &erp.art_compdepot d WHERE d.comp_num=g.comp_num AND d.depot_cod=dep.depot),'AA') prfsto_cod,
         Nvl((SELECT prfrupt_cod FROM &erp.art_compdepot d WHERE d.comp_num=g.comp_num AND d.depot_cod=dep.depot),'STMINI_ECO') prfrupt_cod,
         LPad(dep.depot,2,'0') depotempl_cod,
         Nvl((SELECT prfempl_cod FROM &erp.art_compdepot d WHERE d.comp_num=g.comp_num AND d.depot_cod=dep.depot),'AUCUN') prfempl_cod,
         Nvl((SELECT prflot_cod FROM &erp.art_compdepot d WHERE d.comp_num=g.comp_num AND d.depot_cod=dep.depot),'AUCUN') prflot_cod,
         Nvl((SELECT prfserie_cod FROM &erp.art_compdepot d WHERE d.comp_num=g.comp_num AND d.depot_cod=dep.depot),'AUCUN') prfserie_cod,
         Nvl((SELECT prfidentstk_cod FROM &erp.art_compdepot d WHERE d.comp_num=g.comp_num AND d.depot_cod=dep.depot),'AUCUN') prfidentstk_cod,
         Nvl((SELECT prfinvent_cod FROM &erp.art_compdepot d WHERE d.comp_num=g.comp_num AND d.depot_cod=dep.depot),'MANUEL') prfinvent_cod,
         0 enreg_id,
         'N' enreg_s,
         NULL rgtcase_id,
         NULL pckcase_id,
         NULL emplfam_cod,
         NULL fournisseur_cod,
         NULL abc_cod,
         NULL emplabc_cod,
         NULL invtabc_cod,
         NULL invtdern_dat,
         NULL invtproch_dat,
         NULL adrstock_cod,
         NULL adrstock2_cod,
         NULL adrstock3_cod,
         NULL adrstock4_cod,
         NULL adrstock5_cod,
         Decode(p.frequence,0,NULL) frequencenbs_val,
         NULL fraisappro_mt,
         NULL delaiplus_nb
  FROM uv_pproduits_depot dep, uv_pproduits p,uv_pproduits_statut_actuel s
      ,&erp.art_gene g
  WHERE p.id=dep.id
    AND s.id=p.id AND (s.statut<900 OR s.statut IS NULL)
    AND p.codgestion in ('3')
    AND (g.gene_cod||g.gene_ind)=p.prodbase
 );

 DELETE FROM se_ART_COMPDEPOT WHERE comp_num=0;

 DELETE se_ART_COMPDEPOT d WHERE EXISTS (SELECT 1 FROM  (SELECT  comp_num, depot_cod
                                                         FROM se_ART_COMPDEPOT
                                                         GROUP BY comp_num, depot_cod   HAVING Count(*)>1) dd
                                                  WHERE dd.comp_num=d.comp_num AND d.depot_cod=dd.depot_cod) ;

 UPDATE se_ART_COMPDEPOT tf SET enreg_id=(select enreg_id FROM &erp.ART_COMPDEPOT f WHERE f.comp_num=tf.comp_num AND f.depot_cod=tf.depot_cod ) ;
 UPDATE se_ART_COMPDEPOT tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 
 UPDATE se_ART_COMPDEPOT SET PRFEMPL_COD ='AVEC' WHERE depot_cod IN ('01','05','06','07') AND PRFEMPL_COD ='AUCUN' ;

CALL uvse_synchroverserp('comp_num@depot_cod','ART_COMPDEPOT','O') ;


 commit;

